Imports System.Data.OracleClient

Public Class frmTableDesign

  Private Enum EnmDesignModel As Integer
    NEW_TABLE
    MODIFY_TABLE
  End Enum

  Private mObjDesignModel As EnmDesignModel
  Private mDrData As OracleDataReader
  Private mCmdData As OracleCommand
  Private mArrMemberProperties As New ArrayList
  Private mObjClsTableDesign As New clsTableDesign
  Private mObjClsAddNewTable As New clsAddNewTable
  Private mObjClsGenClas As New clsGenClas
  Private mArrTemplateModel() As String

  Public mIntTableId As Integer

  Private Sub ResetControls()
    Dim lStrDBStyle As String = String.Empty
    Dim lArrDBStyle As String()

    txtTableName.Clear()
    txtTableShortName.Clear()
    txtTableDesc.Clear()
    cboClntSrvrIndc.SelectedIndex = 0

    cboDBType.Items.Clear()
    cboDBType.Items.Add(ALL)
    mFnReadValue(TM_DATABASE_STYLE, String.Empty, "Oracle,SqlCE", lStrDBStyle, Application.StartupPath + "\" + DDL_TEMPLATE_FILE)
    lArrDBStyle = Split(Split(lStrDBStyle, Chr(0))(0), ",")
    For Each lStr As String In lArrDBStyle
      cboDBType.Items.Add(lStr)
    Next
    cboDBType.SelectedIndex = 0

    cboGnrtType.Items.Clear()
    cboGnrtType.Items.Add(ALL)
    mArrTemplateModel = mFnReadChildNodes(TM_TEMPLATE_MODEL, Application.StartupPath + "\" + CODE_TEMPLATE_FILE).Split(" ")
    For Each lStrValue As String In mArrTemplateModel
      If mFnReadAttribute(TM_TEMPLATE_MODEL, lStrValue, TM_CLIENT_INDC, Application.StartupPath + "\" + CODE_TEMPLATE_FILE) = CLIENT_INDC_BE _
      Or mFnReadAttribute(TM_TEMPLATE_MODEL, lStrValue, TM_CLIENT_INDC, Application.StartupPath + "\" + CODE_TEMPLATE_FILE) = CLIENT_INDC_FE _
      Or lStrValue = DDL Then
        cboGnrtType.Items.Add(lStrValue)
      End If
    Next
    cboGnrtType.SelectedIndex = 0

    ctlColumnsRecord.Rows.Clear()
  End Sub

  Private Function PopulateColmuns(ByVal aStrTableName As String, ByVal aIntTableId As Integer) As Boolean
    Dim lStrQuery As String = String.Empty
    Dim lIntRowIndex As Integer

    mPrRefreshConn()
    lStrQuery = "SELECT TABM_CLNT_SRVR_INDC,TABM_TABLE_SHORT_NAME,TTD_TABLE_DESC FROM TRDX_TABLES_MSTR,TRDX_TABLES_DTLS WHERE TABM_TABLE_NAME = '" + UCase(aStrTableName) + "'"
    lStrQuery = lStrQuery & " AND TABM_TABLE_ID  = " & aIntTableId
    lStrQuery = lStrQuery & " AND TTD_TABLE_ID=TABM_TABLE_ID"
    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader

    If mDrData.HasRows Then
      While mDrData.Read
        txtTableShortName.Text = mDrData.Item("TABM_TABLE_SHORT_NAME")
        txtTableDesc.Text = mDrData.Item("TTD_TABLE_DESC")
        cboClntSrvrIndc.Text = mDrData.Item("TABM_CLNT_SRVR_INDC")
      End While
    Else
      txtTableShortName.Clear()
      txtTableDesc.Clear()
      cboClntSrvrIndc.SelectedIndex = 0
      MsgBox("Error", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Function
    End If

    lStrQuery = "SELECT * FROM TRDX_TABLE_COLUMNS"
    lStrQuery = lStrQuery & " WHERE TAB_TABLE_ID = " & aIntTableId
    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader

    If mDrData.HasRows Then
      ctlColumnsRecord.Rows.Clear()
      While mDrData.Read
        lIntRowIndex = ctlColumnsRecord.Rows.Add
        With ctlColumnsRecord.Rows(lIntRowIndex)
          .Cells("clmSortId").Value = CInt(mDrData.Item("TAB_COL_ID"))
          .Cells("clmClmName").Value = mDrData.Item("TAB_COL_NAME")
          .Cells("clmNull").Value = mDrData.Item("TAB_COL_NULL_INDC")
          .Cells("clmLength").Value = mDrData.Item("TAB_COL_DATA_LENGTH")
          .Cells("clmPrecision").Value = mDrData.Item("TAB_COL_DATA_PRECISION")
          .Cells("clmBasicDT").Value = mDrData.Item("TAB_COL_BASIC_DATA_TYPE")
          .Cells("clmFECategory").Value = mDrData.Item("TAB_COL_DATA_TYPE")
          .Cells("clmClientSrvIndc").Value = cboClntSrvrIndc.Text
          .Cells("clmVariableReference").Value = mDrData.Item("TAB_VRBL_SERIAL_NMBR")
          GetDataCategory(Replace(mDrData.Item("TAB_COL_DATA_TYPE").ToString, "cls", String.Empty), _
                          .Cells("clmFEDataType").Value, _
                          .Cells("clmBEDataType").Value, _
                          .Cells("clmClmType").Value, _
                          .Cells("clmClmTypeValueMember").Value, _
                          .Cells("clmBEDBDataType").Value)
        End With
      End While
    Else
      ctlColumnsRecord.Rows.Clear()
      MsgBox("Error", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Function
    End If

    lStrQuery = "SELECT * FROM TRDX_TABL_INDX T "
    lStrQuery = lStrQuery & " WHERE T.TTL_TABL_IDXX = " & aIntTableId
    lStrQuery = lStrQuery & " AND T.TTL_INDX_TYPE='P' "
    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader
    If mDrData.HasRows Then
      While mDrData.Read
        For Each lObjRow As DataGridViewRow In ctlColumnsRecord.Rows
          If mDrData.Item("ttl_colm_idxx") = lObjRow.Cells("clmSortId").Value Then
            lObjRow.Cells("clmPK").Value = "Y"
          End If
        Next
      End While
    End If

    ctlColumnsRecord.Sort(ctlColumnsRecord.Columns("clmSortId"), System.ComponentModel.ListSortDirection.Ascending)
  End Function

  Private Sub GetDataCategory(ByVal aStrFECategory As String _
                             , ByRef aStrFEDT As String _
                             , ByRef aStrBEDT As String _
                             , ByRef aStrDataDesc As String _
                             , ByRef aStrDataCategory As String _
                             , ByRef aStrBEDBDataType As String)
    Dim lStrQuery As String = String.Empty
    Dim lCmdData As OracleCommand
    Dim lDrData As OracleDataReader

    mPrRefreshConn()
    lStrQuery = "SELECT * FROM TRDX_DATA_CATEGORY_MSTR T WHERE T.TDCM_FRNT_END_DATA_CATEGORY='" & aStrFECategory & "'"
    lCmdData = New OracleCommand(lStrQuery, con)
    lDrData = lCmdData.ExecuteReader
    If mDrData.HasRows Then
      While lDrData.Read
        aStrFEDT = lDrData.Item("TDCM_FRNT_END_DATA_TYPE")
        aStrBEDT = lDrData.Item("TDCM_BACK_END_DATA_TYPE")
        aStrDataDesc = lDrData.Item("TDCM_DESC")
        aStrDataCategory = lDrData.Item("TDCM_DATA_CTGRY")
        aStrBEDBDataType = lDrData.Item("TDCM_DATABASE_DATA_TYPE")
      End While
    Else
      aStrFEDT = String.Empty
      aStrBEDT = String.Empty
      aStrDataDesc = String.Empty
      aStrDataCategory = String.Empty
      aStrBEDBDataType = String.Empty
    End If
  End Sub

  Private Function SelectColmun(ByVal aIntRowIndex As Integer, ByVal aStrClmName As String) As Boolean

    Dim lObjFrmColumnNameSelection As New digSelectColumn
    Dim lStrQuery As String = String.Empty
    Dim lCmdData As OracleCommand
    Dim lDrData As OracleDataReader

    lObjFrmColumnNameSelection.mStrColumnName = aStrClmName
    If lObjFrmColumnNameSelection.ShowDialog() = Windows.Forms.DialogResult.Cancel Then Return False

    With ctlColumnsRecord.Rows(aIntRowIndex)
      .Cells("clmClmName").Value = txtTableShortName.Text.Trim & "_" & lObjFrmColumnNameSelection.mObjRtn.mPrpDisplayMember

      lStrQuery = " SELECT * FROM TRDX_APLCTN_VRBLS,TRDX_DATA_CATEGORY_MSTR"
      lStrQuery = lStrQuery & " WHERE TAV_DATA_CTGRY=TDCM_DATA_CTGRY AND TAV_SERIAL_NMBR=" & lObjFrmColumnNameSelection.mObjRtn.mPrpValueMember
      lCmdData = New OracleCommand(lStrQuery, con)
      lDrData = lCmdData.ExecuteReader
      If lDrData.HasRows Then
        While lDrData.Read
          .Cells("clmPrecision").Value = lDrData.Item("TDCM_DATA_PRECISION")
          .Cells("clmFEDataType").Value = lDrData.Item("TDCM_FRNT_END_DATA_TYPE")
          .Cells("clmBEDataType").Value = lDrData.Item("TDCM_BACK_END_DATA_TYPE")
          .Cells("clmFECategory").Value = "cls" & lDrData.Item("TDCM_FRNT_END_DATA_CATEGORY")
          .Cells("clmClmType").Value = lDrData.Item("TDCM_DESC")
          .Cells("clmClmTypeValueMember").Value = lDrData.Item("TDCM_DATA_CTGRY")
          .Cells("clmLength").Value = lDrData.Item("TDCM_DATA_LENGTH")
          .Cells("clmBasicDT").Value = lDrData.Item("TDCM_DATABASE_DATA_TYPE")
          .Cells("clmBEDBDataType").Value = lDrData.Item("TDCM_DATABASE_DATA_TYPE")
        End While
      End If
    End With
    Return True
  End Function

  Private Function SaveTable(ByVal aStrTableName As String, ByVal aStrShortName As String, ByVal aIntTableId As Integer, ByVal aStrClntSrvrIndc As String, ByVal aStrTableDesc As String) As Boolean
    Dim lBlnVld As Boolean
    Dim lIntTableId As Integer = 0
    Dim lStrTableName As String = String.Empty
    Dim lStrShortName As String = String.Empty

    lStrTableName = aStrTableName
    lStrShortName = aStrShortName
    lIntTableId = aIntTableId

    lIntTableId = mObjClsAddNewTable.mPrAddNewTable _
                        (UCase(lStrTableName) _
                        , "USERS" _
                        , aStrClntSrvrIndc _
                        , UCase(lStrShortName) _
                        , aStrTableDesc _
                        , (mObjDesignModel = EnmDesignModel.MODIFY_TABLE) _
                        , False)

    lBlnVld = mObjClsAddNewTable.mPrRemoveTableDetails(UCase(lStrTableName))

    lBlnVld = lBlnVld And mObjClsAddNewTable.mPrAddTableDetails(UCase(lStrTableName), UCase(lStrShortName), ctlColumnsRecord)

    If lBlnVld = False Then
      mObjClsAddNewTable.mPrRemoveTableDetails(UCase(lStrTableName))
      mObjClsAddNewTable.mPrRemoveTable(lIntTableId)
    End If
    If lBlnVld = True Then mIntTableId = lIntTableId
    Return lBlnVld
  End Function

  Private Function GenerateDDL(ByVal aStrTableName As String, ByVal aDgvColmunDgv As DataGridView, ByVal aStrFolderPath As String) As String
    Dim lStrFileList As String = String.Empty

    If UCase(cboDBType.Text.Trim) = ALL Then
      For Each lStrItem As String In cboDBType.Items
        If UCase(lStrItem.Trim) <> ALL Then
          lStrFileList = lStrFileList & mObjClsGenClas.mFnGnrtDBScript(aStrTableName, lStrItem.Trim, aDgvColmunDgv, aStrFolderPath & "\" & lStrItem.Trim) & vbCrLf & "  "
        End If
      Next
    Else
      lStrFileList = lStrFileList & mObjClsGenClas.mFnGnrtDBScript(aStrTableName, cboDBType.Text.Trim, aDgvColmunDgv, aStrFolderPath) & vbCrLf & "  "
    End If
    Return lStrFileList
  End Function

  Private Function GenerateCode(ByVal aStrTemplateModel As String, ByVal aStrTableName As String, ByVal aStrTableShrtName As String, ByVal aStrTableDesc As String, ByVal aDgvColmunDgv As DataGridView, ByVal aStrFolderPath As String) As String
    Dim lStrFileList As String = String.Empty
    Dim lStrModels As String = String.Empty
    Dim lStrFileName As String = String.Empty
    Dim lStrModelName As String = String.Empty
    Dim lStrSaveClientIndc As String = String.Empty
    Dim lArrModels() As String

    mFnReadValue(TM_TEMPLATE_MODEL, aStrTemplateModel, String.Empty, lStrModels, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
    lStrSaveClientIndc = mFnReadAttribute(TM_TEMPLATE_MODEL, aStrTemplateModel, TM_CLIENT_INDC, Application.StartupPath + "\" + CODE_TEMPLATE_FILE)
    lArrModels = lStrModels.Split(",")

    For Each lStrModel As String In lArrModels
      lStrModelName = lStrModel.Split("|")(0)
      lStrFileName = lStrModel.Split("|")(1)
      lStrFileName = lStrFileName.Replace("%" & TM_TABLE_NAME & "%", StrConv(aStrTableName.Replace(TABLE_HEAD, String.Empty).Replace("_", " "), VbStrConv.ProperCase).Replace(" ", String.Empty))
      lStrFileList = lStrFileList & mObjClsGenClas.mFnGnrtCode(aStrTableName, aStrTableShrtName, aStrTableDesc, String.Empty, String.Empty, aDgvColmunDgv, aStrFolderPath & "\" & aStrTemplateModel, lStrFileName, lStrModelName, lStrSaveClientIndc) & vbCrLf & "  "
    Next

    Return lStrFileList
  End Function

  '-----------------------------------------------------------------------------------
  ' ControlsEvent
  '-----------------------------------------------------------------------------------

  Private Sub frmTableDesign_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    mPrRefreshConn()
    'PopulateDataCategory()

    ResetControls()
    radNew.Checked = True


  End Sub

  Private Sub radNew_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radNew.CheckedChanged
    If radNew.Checked = True Then
      ResetControls()
      mObjDesignModel = EnmDesignModel.NEW_TABLE
      mIntTableId = 0
      cmdAddFK.Enabled = False
      cmdAddIndex.Enabled = False
      cmdGenerate.Enabled = False
    End If
  End Sub

  Private Sub radModify_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radModify.CheckedChanged
    If radModify.Checked = True Then
      ResetControls()
      mObjDesignModel = EnmDesignModel.MODIFY_TABLE
      cmdAddFK.Enabled = True
      cmdAddIndex.Enabled = True
      cmdGenerate.Enabled = True
    End If
  End Sub

  Private Sub cmdSelectTableName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSelectTableName.Click
    If mObjDesignModel = EnmDesignModel.NEW_TABLE Then
      Dim lIntTableNameLength As Integer = 0
      Dim lObjFrmTableNameSelection As New frmTableNameAbbreviation
      lObjFrmTableNameSelection.Text = "Select Table Name"
      lObjFrmTableNameSelection.ShowDialog()
      lIntTableNameLength = lObjFrmTableNameSelection.mStrColumnName.Length
      If lIntTableNameLength <> 0 Then
        txtTableName.Text = TABLE_HEAD + UCase(lObjFrmTableNameSelection.mStrColumnName.Substring(0, lIntTableNameLength - 1))
        mIntTableId = 0
      End If
    ElseIf mObjDesignModel = EnmDesignModel.MODIFY_TABLE Then
      FrmDisplayGrid.sCallingPurpose = gENMU_PUEPOSE.Md_Tables
      FrmDisplayGrid.sCallingControl = "txtTableName"
      FrmDisplayGrid.ShowDialog()
      If Len(txtTableName.Text.Trim) = 0 Then
        'MsgBox("Please select one table", MsgBoxStyle.Information, gCONST_MSG_HEADER)
        Exit Sub
      Else
        PopulateColmuns(txtTableName.Text.Trim, mIntTableId)
      End If
    End If
  End Sub

  Private Sub cmdAddIndex_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddIndex.Click

    If Trim(txtTableName.Text) = "" Then
      MsgBox("Please Enter table name", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    ElseIf Trim(txtTableShortName.Text) = "" Then
      MsgBox("Please Enter table short name", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    ElseIf ctlColumnsRecord.Rows.Count = 0 Then
      MsgBox("Please insert columns", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End If

    If SaveTable(txtTableName.Text.Trim, txtTableShortName.Text.Trim, mIntTableId, cboClntSrvrIndc.Text.Trim, txtTableDesc.Text.Trim) = False Then
      MsgBox("Error", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End If

    frmIndexGenretor.Close()
    frmIndexGenretor.mTableName = txtTableName.Text
    frmIndexGenretor.ShowDialog(Me)
    If mIntTableId <> 0 Then _
      PopulateColmuns(txtTableName.Text.Trim, mIntTableId)
  End Sub

  Private Sub ctlColumnsRecord_CellMouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles ctlColumnsRecord.CellMouseDoubleClick
    If e.RowIndex < 0 Then Exit Sub
    SelectColmun(e.RowIndex, _
      ctlColumnsRecord.Rows(e.RowIndex).Cells("clmClmName").Value.ToString.Substring( _
      ctlColumnsRecord.Rows(e.RowIndex).Cells("clmClmName").Value.ToString.IndexOf(txtTableShortName.Text.Trim & "_") + txtTableShortName.Text.Length + 1))
    ctlColumnsRecord.Sort(ctlColumnsRecord.Columns("clmSortId"), System.ComponentModel.ListSortDirection.Ascending)
  End Sub

  Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
    Dim lIntNewRow As Integer
    Dim lLngColumnSrno As Long
    Dim lLngMaxColumnSrno As Long

    If Trim(txtTableName.Text) = "" Then
      MsgBox("Please Enter table name", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    ElseIf Trim(txtTableShortName.Text) = "" Then
      MsgBox("Please Enter table short name", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End If

    lIntNewRow = ctlColumnsRecord.Rows.Add()
    If SelectColmun(lIntNewRow, String.Empty) = False Then
      ctlColumnsRecord.Rows.RemoveAt(lIntNewRow)
      Exit Sub
    End If

    ctlColumnsRecord.Rows(lIntNewRow).Cells("clmPK").Value = "N"
    ctlColumnsRecord.Rows(lIntNewRow).Cells("clmNull").Value = "N"
    lLngColumnSrno = mObjClsTableDesign.GetMaxColumSrno() + 1
    For Each lObjRow As DataGridViewRow In ctlColumnsRecord.Rows
      If lObjRow.Cells("clmVariableReference").Value >= lLngMaxColumnSrno Then
        lLngMaxColumnSrno = lObjRow.Cells("clmVariableReference").Value
      End If
    Next
    If lLngColumnSrno <= lLngMaxColumnSrno Then lLngColumnSrno = lLngMaxColumnSrno + 1
    ctlColumnsRecord.Rows(lIntNewRow).Cells("clmVariableReference").Value = lLngColumnSrno
    ctlColumnsRecord.Rows(lIntNewRow).Cells("clmSortId").Value = ctlColumnsRecord.Rows.Count
    ctlColumnsRecord.Sort(ctlColumnsRecord.Columns("clmSortId"), System.ComponentModel.ListSortDirection.Ascending)
  End Sub

  Private Sub cboGnrtType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboGnrtType.SelectedIndexChanged
    If UCase(cboGnrtType.Text.Trim) = ALL Or UCase(cboGnrtType.Text.Trim) = DDL Then
      cboDBType.Enabled = True
    Else
      cboDBType.Enabled = False
    End If
  End Sub

  Private Sub cmdRemove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRemove.Click
    If ctlColumnsRecord.SelectedRows.Count = 0 Then
      MsgBox("Please select one row", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End If
    If MsgBox("Clear column - " & ctlColumnsRecord.SelectedRows(0).Cells("clmClmName").Value.ToString & " ?", MsgBoxStyle.Question Or MsgBoxStyle.YesNo, gCONST_MSG_HEADER) = MsgBoxResult.Yes Then _
      ctlColumnsRecord.Rows.Remove(ctlColumnsRecord.SelectedRows(0))
    ctlColumnsRecord.Sort(ctlColumnsRecord.Columns("clmSortId"), System.ComponentModel.ListSortDirection.Ascending)
  End Sub

  Private Sub cmdClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClear.Click
    If MsgBox("Clear all columns ?", MsgBoxStyle.Question Or MsgBoxStyle.YesNo, gCONST_MSG_HEADER) = MsgBoxResult.Yes Then _
     ctlColumnsRecord.Rows.Clear()
  End Sub

  Private Sub cmdUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUp.Click
    Dim lIntOldIndex As Integer
    With ctlColumnsRecord
      If .SelectedRows.Count = 0 Then
        MsgBox("Please select one row", MsgBoxStyle.Information, gCONST_MSG_HEADER)
        Exit Sub
      End If
      If .SelectedRows(0).Index <= 0 Then Exit Sub
      lIntOldIndex = .SelectedRows(0).Cells("clmSortId").Value
      .SelectedRows(0).Cells("clmSortId").Value = .Rows(.SelectedRows(0).Index - 1).Cells("clmSortId").Value
      .Rows(.SelectedRows(0).Index - 1).Cells("clmSortId").Value = lIntOldIndex
      .Sort(.Columns("clmSortId"), System.ComponentModel.ListSortDirection.Ascending)
      .FirstDisplayedScrollingRowIndex = .SelectedRows(0).Index
    End With
  End Sub

  Private Sub cmdDown_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDown.Click
    Dim lIntOldIndex As Integer
    With ctlColumnsRecord
      If .SelectedRows.Count = 0 Then
        MsgBox("Please select one row", MsgBoxStyle.Information, gCONST_MSG_HEADER)
        Exit Sub
      End If
      If .SelectedRows(0).Index >= .RowCount - 1 Then Exit Sub
      lIntOldIndex = .SelectedRows(0).Cells("clmSortId").Value
      .SelectedRows(0).Cells("clmSortId").Value = .Rows(.SelectedRows(0).Index + 1).Cells("clmSortId").Value
      .Rows(.SelectedRows(0).Index + 1).Cells("clmSortId").Value = lIntOldIndex
      .Sort(.Columns("clmSortId"), System.ComponentModel.ListSortDirection.Ascending)
      .FirstDisplayedScrollingRowIndex = .SelectedRows(0).Index
    End With
  End Sub

  Private Sub cmdManage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdManage.Click
    frmGnrVar.Close()
    frmGnrVar.ShowDialog()
  End Sub

  Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
    Dim lBlnVld As Boolean

    If Trim(txtTableName.Text) = "" Then
      MsgBox("Please Enter table name", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    ElseIf Trim(txtTableShortName.Text) = "" Then
      MsgBox("Please Enter table short name", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    ElseIf ctlColumnsRecord.Rows.Count = 0 Then
      MsgBox("Please insert columns", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End If

    If MsgBox("Save table - " & txtTableName.Text.Trim & " ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Question, gCONST_MSG_HEADER) = MsgBoxResult.No Then Exit Sub

    lBlnVld = SaveTable(txtTableName.Text.Trim, txtTableShortName.Text.Trim, mIntTableId, cboClntSrvrIndc.Text.Trim, txtTableDesc.Text.Trim)
   
    If lBlnVld = False Then
      MsgBox("Error", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    Else
      cmdAddIndex.Enabled = True
      cmdAddFK.Enabled = True
      cmdGenerate.Enabled = True
      MsgBox("Save table successfully", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End If

  End Sub

  Private Sub cmdGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGenerate.Click
    Dim lStrFileList As String = String.Empty
    Dim lStrFile As String = String.Empty
    Dim lStrFolderPath As String = String.Empty

    If Trim(txtTableName.Text) = "" Then
      MsgBox("Please Enter table name", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    ElseIf Trim(txtTableShortName.Text) = "" Then
      MsgBox("Please Enter table short name", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    ElseIf ctlColumnsRecord.Rows.Count = 0 Then
      MsgBox("Please insert columns", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End If

    If SaveTable(txtTableName.Text.Trim, txtTableShortName.Text.Trim, mIntTableId, cboClntSrvrIndc.Text.Trim, txtTableDesc.Text.Trim) = False Then
      MsgBox("Error", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End If

    If SelectFolder(lStrFolderPath) = False Then  Exit Sub

    Try
      If UCase(cboGnrtType.Text.Trim) = ALL Then
        lStrFileList = vbCrLf & DDL & " Files:" & vbCrLf & "  "
        lStrFileList = lStrFileList & GenerateDDL(Trim(txtTableName.Text), ctlColumnsRecord, lStrFolderPath & "\" & DDL)

        For Each lStrValue As String In mArrTemplateModel
          If lStrValue.Trim <> ALL And lStrValue.Trim <> DDL Then
            If mFnReadAttribute(TM_TEMPLATE_MODEL, lStrValue, TM_CLIENT_INDC, Application.StartupPath + "\" + CODE_TEMPLATE_FILE) = CLIENT_INDC_BE _
            Or mFnReadAttribute(TM_TEMPLATE_MODEL, lStrValue, TM_CLIENT_INDC, Application.StartupPath + "\" + CODE_TEMPLATE_FILE) = CLIENT_INDC_FE Then
              lStrFileList = lStrFileList & vbCrLf & lStrValue.Trim & " Files:" & vbCrLf & "  "
              lStrFileList = lStrFileList & GenerateCode(lStrValue.Trim, Trim(txtTableName.Text), Trim(txtTableShortName.Text), Trim(txtTableDesc.Text), ctlColumnsRecord, lStrFolderPath)
            End If
          End If
        Next

      ElseIf UCase(cboGnrtType.Text.Trim) = DDL Then
        lStrFileList = vbCrLf & DDL & " Files:" & vbCrLf & "  "
        lStrFileList = lStrFileList & GenerateDDL(Trim(txtTableName.Text), ctlColumnsRecord, lStrFolderPath)
      Else
        lStrFileList = lStrFileList & vbCrLf & cboGnrtType.Text.Trim & " Files:" & vbCrLf & "  "
        lStrFileList = lStrFileList & GenerateCode(cboGnrtType.Text.Trim, Trim(txtTableName.Text), Trim(txtTableShortName.Text), Trim(txtTableDesc.Text), ctlColumnsRecord, lStrFolderPath)
      End If

      If lStrFileList.Length >= 1000 Then lStrFileList = lStrFileList.Remove(1000) & vbCrLf & " ..."
      MsgBox("Generate successfully - " & lStrFileList, MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    Catch ex As Exception
      MsgBox("Error" & vbCrLf & ex.Message, MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End Try

  End Sub

  Private Sub cmdAddFK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddFK.Click

    If Trim(txtTableName.Text) = "" Then
      MsgBox("Please Enter table name", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    ElseIf Trim(txtTableShortName.Text) = "" Then
      MsgBox("Please Enter table short name", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    ElseIf ctlColumnsRecord.Rows.Count = 0 Then
      MsgBox("Please insert columns", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End If

    If SaveTable(txtTableName.Text.Trim, txtTableShortName.Text.Trim, mIntTableId, cboClntSrvrIndc.Text.Trim, txtTableDesc.Text.Trim) = False Then
      MsgBox("Error", MsgBoxStyle.Information, gCONST_MSG_HEADER)
      Exit Sub
    End If

    frmForeignKeyGenretor.Close()
    frmForeignKeyGenretor.mStrTableName = txtTableName.Text
    frmForeignKeyGenretor.ShowDialog(Me)
    If mIntTableId <> 0 Then _
      PopulateColmuns(txtTableName.Text.Trim, mIntTableId)
  End Sub

End Class